package com.example.server.db;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.eclipse.jetty.util.log.Log;
import org.eclipse.jetty.util.log.Logger;

public class DBManager {

	private final static Logger log = Log.getLogger(DBManager.class);
	private String jndiName = "java:comp/env/jdbc/db";
	private DataSource dataSource = null;
	private static DBManager instance = null;

	private ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();

	public void setConnection(Connection connection) {
		this.threadLocal.set(connection);
	}

	public DBManager() {
		log.debug("DBManager start ...");
		instance = this;
		log.debug("DBManager end ...");
	}

	public static DBManager getInstance() {
		if (instance == null) {
			instance = new DBManager();
		}
		return instance;
	}

	public void getConnection() throws NamingException, SQLException {
		log.debug("getConnection start ...");

		if (threadLocal.get() == null || threadLocal.get().isClosed()) {
			DataSource ds = getDataSource();
			if (ds != null) {
				log.debug("getConnection return connection");
				setConnection(ds.getConnection());
			}
		}
		log.debug("getConnection end ...");
	}

	public DataSource getDataSource() throws NamingException {
		if (dataSource == null) {
			InitialContext ic = new InitialContext();
			dataSource = (DataSource) ic.lookup(jndiName);
		}
		return dataSource;
	}

	public void releaseConnection() {
		log.debug("releaseConnection start ...");
		try {
			if (threadLocal.get() == null || threadLocal.get().isClosed()) {
				log.debug("releaseConnection connection is null");
				return;
			}
			threadLocal.get().close();
		} catch (SQLException e) {
			e.printStackTrace();
			log.warn("releaseConnection end ...", e);
		}
		log.debug("releaseConnection end ...");
	}

	public Map<String, Object> findSimpleResult(String sql, List<?> sqlValues) throws NamingException, SQLException {
		log.debug("findSimpleResult start ...");
		log.info("findSimpleResult " + sql);

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			Connection conn = threadLocal.get();
			if (conn != null && !conn.isClosed()) {
				ps = conn.prepareStatement(sql);

				if (sqlValues != null && sqlValues.size() > 0) { // 当sql语句中存在占位符时
					setSqlValues(ps, sqlValues);
				}

				rs = ps.executeQuery();
				ResultSetMetaData rsmd = null;
				while (rs.next()) {
					Map<String, Object> row = new LinkedHashMap<String, Object>();
					if (rsmd == null) {
						rsmd = rs.getMetaData();
					}
					for (int i = 1; i <= rsmd.getColumnCount(); i++) {
						row.put(rsmd.getColumnLabel(i), rs.getObject(i));
					}

					return row;
				}
			}else{
				log.debug("connection is null  or  close");
			}

		} finally {
			if (rs != null)
				rs.close();
			if (ps != null)
				ps.close();
			log.debug("simpleQueryOne end ...");
		}
		return null;
	}

	/**
	 * 给sql语句中的占位符赋值
	 * 
	 * @param pst
	 * @param sqlValues
	 */
	private void setSqlValues(PreparedStatement pst, List<?> sqlValues) {
		for (int i = 0; i < sqlValues.size(); i++) {
			try {
				pst.setObject(i + 1, sqlValues.get(i));
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	/**
	 * 增加、删除、改
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public boolean updateByPreparedStatement(String sql, List<Object> params) throws SQLException {
		boolean flag = false;
		int result = -1;
		PreparedStatement pstmt = threadLocal.get().prepareStatement(sql);
		int index = 1;
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		result = pstmt.executeUpdate();
		flag = result > 0 ? true : false;
		return flag;
	}

	/**
	 * 查询多条记录
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> findModeResult(String sql, List<Object> params) throws SQLException {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		int index = 1;
		PreparedStatement pstmt = threadLocal.get().prepareStatement(sql);
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		ResultSet resultSet = pstmt.executeQuery();
		ResultSetMetaData metaData = resultSet.getMetaData();
		int cols_len = metaData.getColumnCount();
		while (resultSet.next()) {
			Map<String, Object> map = new HashMap<String, Object>();
			for (int i = 0; i < cols_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				map.put(cols_name, cols_value);
			}
			list.add(map);
		}

		return list;
	}

	/**
	 * 通过反射机制查询单条记录
	 * 
	 * @param sql
	 * @param params
	 * @param cls
	 * @return
	 * @throws Exception
	 */
	public <T> T findSimpleRefResult(String sql, List<Object> params, Class<T> cls) throws Exception {
		T resultObject = null;
		int index = 1;
		PreparedStatement pstmt = threadLocal.get().prepareStatement(sql);
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		ResultSet resultSet = pstmt.executeQuery();
		ResultSetMetaData metaData = resultSet.getMetaData();
		int cols_len = metaData.getColumnCount();
		while (resultSet.next()) {
			// 通过反射机制创建一个实例
			resultObject = cls.newInstance();
			for (int i = 0; i < cols_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				Field field = cls.getDeclaredField(cols_name);
				field.setAccessible(true); // 打开javabean的访问权限
				field.set(resultObject, cols_value);
			}
		}
		return resultObject;

	}

	/**
	 * 通过反射机制查询多条记录
	 * 
	 * @param sql
	 * @param params
	 * @param cls
	 * @return
	 * @throws Exception
	 */
	public <T> List<T> findMoreRefResult(String sql, List<Object> params, Class<T> cls) throws Exception {
		List<T> list = new ArrayList<T>();
		int index = 1;
		PreparedStatement pstmt = threadLocal.get().prepareStatement(sql);
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		ResultSet resultSet = pstmt.executeQuery();
		ResultSetMetaData metaData = resultSet.getMetaData();
		int cols_len = metaData.getColumnCount();
		while (resultSet.next()) {
			// 通过反射机制创建一个实例
			T resultObject = cls.newInstance();
			for (int i = 0; i < cols_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				Field field = cls.getDeclaredField(cols_name);
				field.setAccessible(true); // 打开javabean的访问权限
				field.set(resultObject, cols_value);
			}
			list.add(resultObject);
		}
		return list;
	}

}
